use tauri_modern_template::database::{
    DatabaseConnection, QueryBuilder, User, AppConfig, LogEntry, 
    SortDirection, PaginationOption, QueryValue, with_transaction
};
use tempfile::tempdir;
use std::path::PathBuf;

#[cfg(test)]
mod database_tests {
    use super::*;

    fn create_test_db() -> DatabaseConnection {
        let temp_dir = tempdir().unwrap();
        let db_path = temp_dir.path().join("test.db");
        DatabaseConnection::new(&db_path).unwrap()
    }

    #[test]
    fn test_database_connection() {
        let db = create_test_db();
        assert!(db.is_connected());
    }

    #[test]
    fn test_database_migrations() {
        let db = create_test_db();
        
        // Run migrations
        db.run_migrations().unwrap();
        
        // Check if tables exist
        let conn = db.get_connection().unwrap();
        let mut stmt = conn.prepare("SELECT name FROM sqlite_master WHERE type='table'").unwrap();
        let table_names: Vec<String> = stmt.query_map([], |row| {
            Ok(row.get::<_, String>(0)?)
        }).unwrap().collect::<Result<Vec<_>, _>>().unwrap();
        
        assert!(table_names.contains(&"app_config".to_string()));
        assert!(table_names.contains(&"logs".to_string()));
        assert!(table_names.contains(&"migrations".to_string()));
    }

    #[test]
    fn test_app_config_operations() {
        let db = create_test_db();
        db.run_migrations().unwrap();
        
        let conn = db.get_connection().unwrap();
        
        // Test default config creation
        let default_config = AppConfig::default();
        
        // Insert config
        conn.execute(
            "INSERT INTO app_config (id, theme, language, auto_start, minimize_to_tray, notifications, shortcuts, created_at, updated_at) 
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
            (
                &default_config.id,
                &default_config.theme,
                &default_config.language,
                default_config.auto_start,
                default_config.minimize_to_tray,
                default_config.notifications,
                serde_json::to_string(&default_config.shortcuts).unwrap(),
                default_config.created_at,
                default_config.updated_at,
            ),
        ).unwrap();
        
        // Retrieve config
        let mut stmt = conn.prepare("SELECT * FROM app_config WHERE id = ?1").unwrap();
        let config = stmt.query_row([&default_config.id], |row| {
            AppConfig::from_row(row)
        }).unwrap();
        
        assert_eq!(config.id, default_config.id);
        assert_eq!(config.theme, default_config.theme);
        assert_eq!(config.language, default_config.language);
    }

    #[test]
    fn test_log_entry_operations() {
        let db = create_test_db();
        db.run_migrations().unwrap();
        
        let conn = db.get_connection().unwrap();
        
        // Create log entry
        let log_entry = LogEntry::new("INFO", "Test log message")
            .with_component("test_component")
            .with_metadata(serde_json::json!({"key": "value"}));
        
        // Insert log entry
        conn.execute(
            "INSERT INTO logs (level, message, component, metadata, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
            (
                &log_entry.level,
                &log_entry.message,
                &log_entry.component,
                log_entry.metadata.as_ref().map(|m| serde_json::to_string(m).unwrap()),
                log_entry.created_at,
            ),
        ).unwrap();
        
        // Retrieve log entry
        let mut stmt = conn.prepare("SELECT * FROM logs ORDER BY created_at DESC LIMIT 1").unwrap();
        let retrieved_log = stmt.query_row([], |row| {
            LogEntry::from_row(row)
        }).unwrap();
        
        assert_eq!(retrieved_log.level, log_entry.level);
        assert_eq!(retrieved_log.message, log_entry.message);
        assert_eq!(retrieved_log.component, log_entry.component);
    }

    #[test]
    fn test_query_builder_basic() {
        let db = create_test_db();
        db.run_migrations().unwrap();
        
        // Test basic query building
        let query_builder = QueryBuilder::new("logs")
            .select(&["level", "message", "created_at"])
            .where_eq("level", QueryValue::String("ERROR".to_string()));
        
        let (sql, params) = query_builder.build_select();
        
        assert!(sql.contains("SELECT level, message, created_at FROM logs"));
        assert!(sql.contains("WHERE level = ?"));
        assert_eq!(params.len(), 1);
    }

    #[test]
    fn test_query_builder_with_sorting() {
        let query_builder = QueryBuilder::new("logs")
            .order_by("created_at", SortDirection::Desc)
            .order_by("level", SortDirection::Asc);
        
        let (sql, _) = query_builder.build_select();
        
        assert!(sql.contains("ORDER BY created_at DESC, level ASC"));
    }

    #[test]
    fn test_query_builder_with_pagination() {
        let query_builder = QueryBuilder::new("logs")
            .paginate(2, 10); // Page 2, 10 items per page
        
        let (sql, _) = query_builder.build_select();
        
        assert!(sql.contains("LIMIT 10 OFFSET 10"));
    }

    #[test]
    fn test_query_builder_complex() {
        let query_builder = QueryBuilder::new("logs")
            .select(&["id", "level", "message"])
            .where_eq("level", QueryValue::String("ERROR".to_string()))
            .where_like("message", "%database%")
            .order_by("created_at", SortDirection::Desc)
            .paginate(1, 20);
        
        let (sql, params) = query_builder.build_select();
        
        assert!(sql.contains("SELECT id, level, message FROM logs"));
        assert!(sql.contains("WHERE level = ? AND message LIKE ?"));
        assert!(sql.contains("ORDER BY created_at DESC"));
        assert!(sql.contains("LIMIT 20 OFFSET 0"));
        assert_eq!(params.len(), 2);
    }

    #[test]
    fn test_query_builder_with_joins() {
        let query_builder = QueryBuilder::new("users")
            .select(&["users.id", "users.username", "profiles.email"])
            .inner_join("profiles", "profiles.user_id = users.id")
            .where_eq("users.is_active", QueryValue::Boolean(true));
        
        let (sql, _) = query_builder.build_select();
        
        assert!(sql.contains("INNER JOIN profiles ON profiles.user_id = users.id"));
    }

    #[test]
    fn test_query_builder_where_in() {
        let query_builder = QueryBuilder::new("logs")
            .where_in("level", vec![
                QueryValue::String("ERROR".to_string()),
                QueryValue::String("WARN".to_string()),
                QueryValue::String("FATAL".to_string()),
            ]);
        
        let (sql, params) = query_builder.build_select();
        
        assert!(sql.contains("WHERE level IN (?, ?, ?)"));
        assert_eq!(params.len(), 3);
    }

    #[test]
    fn test_transaction_success() {
        let db = create_test_db();
        db.run_migrations().unwrap();
        
        let conn = db.get_connection().unwrap();
        
        let result = with_transaction(&conn, |tx| {
            // Insert multiple log entries in a transaction
            tx.execute(
                "INSERT INTO logs (level, message, created_at) VALUES (?1, ?2, ?3)",
                ("INFO", "Log 1", chrono::Utc::now()),
            )?;
            
            tx.execute(
                "INSERT INTO logs (level, message, created_at) VALUES (?1, ?2, ?3)",
                ("ERROR", "Log 2", chrono::Utc::now()),
            )?;
            
            Ok(())
        });
        
        assert!(result.is_ok());
        
        // Verify both entries were inserted
        let count: i64 = conn.query_row("SELECT COUNT(*) FROM logs", [], |row| {
            row.get(0)
        }).unwrap();
        
        assert_eq!(count, 2);
    }

    #[test]
    fn test_transaction_rollback() {
        let db = create_test_db();
        db.run_migrations().unwrap();
        
        let conn = db.get_connection().unwrap();
        
        let result = with_transaction(&conn, |tx| {
            // Insert one entry
            tx.execute(
                "INSERT INTO logs (level, message, created_at) VALUES (?1, ?2, ?3)",
                ("INFO", "Log 1", chrono::Utc::now()),
            )?;
            
            // Cause an error to trigger rollback
            tx.execute(
                "INSERT INTO non_existent_table (column) VALUES (?1)",
                ("value",),
            )?;
            
            Ok(())
        });
        
        assert!(result.is_err());
        
        // Verify no entries were inserted due to rollback
        let count: i64 = conn.query_row("SELECT COUNT(*) FROM logs", [], |row| {
            row.get(0)
        }).unwrap();
        
        assert_eq!(count, 0);
    }

    #[test]
    fn test_pagination_option() {
        let pagination = PaginationOption::new(3, 15);
        
        assert_eq!(pagination.page, 3);
        assert_eq!(pagination.per_page, 15);
        assert_eq!(pagination.offset(), 30); // (3-1) * 15
        assert_eq!(pagination.limit(), 15);
    }

    #[test]
    fn test_sort_direction() {
        assert_eq!(SortDirection::Asc.to_sql(), "ASC");
        assert_eq!(SortDirection::Desc.to_sql(), "DESC");
    }

    #[test]
    fn test_query_value_types() {
        let string_val = QueryValue::String("test".to_string());
        let int_val = QueryValue::Integer(42);
        let float_val = QueryValue::Float(3.14);
        let bool_val = QueryValue::Boolean(true);
        let null_val = QueryValue::Null;
        
        // These would be tested in actual SQL execution context
        // Here we just verify the enum variants exist
        match string_val {
            QueryValue::String(s) => assert_eq!(s, "test"),
            _ => panic!("Expected String variant"),
        }
        
        match int_val {
            QueryValue::Integer(i) => assert_eq!(i, 42),
            _ => panic!("Expected Integer variant"),
        }
    }

    #[test]
    fn test_model_trait_implementations() {
        // Test that our models implement the Model trait correctly
        assert_eq!(User::table_name(), "users");
        assert_eq!(User::primary_key(), "id");
        
        assert_eq!(AppConfig::table_name(), "app_config");
        assert_eq!(AppConfig::primary_key(), "id");
        
        assert_eq!(LogEntry::table_name(), "logs");
        assert_eq!(LogEntry::primary_key(), "id");
    }
}
